# -*- coding:utf-8 -*-

"""
MySQLet class
~~~~~~~~~~~~
这个类对pymysql进行封装，以简化对mysql数据库的操作

:copyright: (c) 2012 by gdlmo.
:license: Apache2, see LICENSE for more details.
"""

import pymysql
from pymysql.err import Error
from config import connectionString


class MySQLet:
    """Connection to a MySQL"""

    def __init__(self, **kwargs):
        try:
            self._conn = pymysql.connect(**connectionString)
            self._cursor = self._conn.cursor()
        except pymysql.err.InternalError as err:
            print('mysql连接错误：' + err)

    def findBySql(self, sql, params={}, limit=0, join='AND'):
        """
        自定义sql语句查找
        limit = 是否需要返回多少行
        params = dict(field=value)
        join = 'AND | OR'
        """
        cursor = self.__getCursor()
        sql = self.__joinWhere(sql, params, join)
        cursor.execute(sql, tuple(params.values()))
        rows = cursor.fetchmany(size=limit) if limit > 0 else cursor.fetchall()
        result = [dict(zip(cursor.column_names, row)) for row in rows] if rows else None
        return result

    def countBySql(self, sql, params={}, join='AND'):
        """自定义sql 统计影响行数"""
        cursor = self.__getCursor()
        sql = self.__joinWhere(sql, params, join)
        cursor.execute(sql, tuple(params.values()))
        result = cursor.fetchone();
        return result[0] if result else 0

    def insert(self, table, param):
        """
        新增一条记录
        param - 数据字典 {field:value...}
        """
        fields = ','.join('`' + k + '`' for k in param.keys())
        values = ','.join(("%s",) * len(param))
        sql = 'INSERT INTO `%s` (%s) VALUES (%s)' % (table, fields, values)
        self._cursor
        self._cursor.execute(sql, tuple(param.values()))
        insert_id = self._cursor.lastrowid
        self._conn.commit()
        return insert_id

    def insertMany(self, table, params):
        """
        新增多条数据

        params - 数据列表 [{field:value...}...]
        table - 表名

        Returns rowcount 影响到行数
        """
        fields = ','.join(params[0].keys())
        inputs = ','.join(("%s",) * len(params[0]))
        values = []
        [values.append(tuple(item.values())) for item in params]

        sql = "INSERT INTO %s (%s) VALUES (" % (table, fields) + inputs + ")"
        self._cursor.executemany(sql, values)
        self._conn.commit()
        return self._cursor.rowcount

    def updateByAttr(self, table, data, params={}, join='AND'):
        """更新数据"""
        fields = ','.join('`' + k + '`=%s' for k in data.keys())
        values = list(data.values())
        values.extend(list(params.values()))
        sql = "UPDATE `%s` SET %s " % (table, fields)
        sql = self.__joinWhere(sql, params, join)
        cursor = self.__getCursor()
        cursor.execute(sql, tuple(values))
        self._conn.commit()
        return cursor.rowcount

    def updateByPk(self, table, data, id, pk='id'):
        """根据主键更新，默认是id为主键"""
        return self.updateByAttr(table, data, {pk: id})

    def deleteByAttr(self, table, params={}, join='AND'):
        """删除数据"""
        fields = ','.join('`' + k + '`=%s' for k in params.keys())
        sql = "DELETE FROM `%s` " % table
        sql = self.__joinWhere(sql, params, join)
        cursor = self.__getCursor()
        cursor.execute(sql, tuple(params.values()))
        self._conn.commit()
        return cursor.rowcount

    def doSQL(self, sql):
        return self._cursor.execute(sql)

    def deleteByPk(self, table, id, pk='id'):
        """根据主键删除，默认是id为主键"""
        return self.deleteByAttr(table, {pk: id})

    def findByAttr(self, table, criteria={}):
        """根據條件查找一條記錄"""
        return self.__query(table, criteria)

    def findByPk(self, table, id, pk='id'):
        return self.findByAttr(table, {'where': '`' + pk + '`=' + str(id)})

    def findAllByAttr(self, table, criteria={}):
        """根據條件查找記錄"""
        return self.__query(table, criteria, True)

    def count(self, table, params={}, join='AND'):
        """根据条件统计行数"""
        sql = 'SELECT COUNT(*) FROM `%s`' % table
        sql = self.__joinWhere(sql, params, join)
        cursor = self.__getCursor()
        cursor.execute(sql, tuple(params.values()))
        result = cursor.fetchone();
        return result[0] if result else 0

    def exit(self, table, params={}, join='AND'):
        """判断是否存在"""
        return self.count(table, params, join) > 0

    def close(self):
        """关闭游标和数据库连接"""
        if self._cursor is not None:
            self._cursor.close()
        self._conn.close()

    def __getCursor(self):
        """获取游标"""
        if self._cursor is None:
            self._cursor = self._conn.cursor()
        return self._cursor

    def __joinWhere(self, sql, params, join):
        """转换params为where连接语句"""
        if params:
            keys, _keys = self.__tParams(params)
            where = ' AND '.join(k + '=' + _k for k, _k in zip(keys, _keys)) if join == 'AND' else ' OR '.join(
                k + '=' + _k for k, _k in zip(keys, _keys))
            sql += ' WHERE ' + where
        return sql

    def __tParams(self, params):
        keys = ['`' + k + '`' for k in params.keys()]
        _keys = ['%s' for k in params.keys()]
        return keys, _keys

    def __query(self, table, criteria, all=False):
        if all is not True:
            criteria['limit'] = 1
        sql = self.__contact_sql(table, criteria)
        cursor = self.__getCursor()
        cursor.execute(sql)
        rows = cursor.fetchall() if all else cursor.fetchone()
        result = [dict(zip(cursor.column_names, row)) for row in rows] if all else dict(
            zip(cursor.column_names, rows)) if rows else None
        return result

    def __contact_sql(self, table, criteria):
        sql = 'SELECT '
        if criteria and type(criteria) is dict:
            # select fields
            if 'select' in criteria:
                fields = criteria['select'].split(',')
                sql += ','.join('`' + field + '`' for field in fields)
            else:
                sql += ' * '
            # table
            sql += ' FROM `%s`' % table
            # where
            if 'where' in criteria:
                sql += ' WHERE ' + criteria['where']
            # group by
            if 'group' in criteria:
                sql += ' GROUP BY ' + criteria['group']
            # having
            if 'having' in criteria:
                sql += ' HAVING ' + criteria['having']
            # order by
            if 'order' in criteria:
                sql += ' ORDER BY ' + criteria['order']
            # limit
            if 'limit' in criteria:
                sql += ' LIMIT ' + str(criteria['limit'])
            # offset
            if 'offset' in criteria:
                sql += ' OFFSET ' + str(criteria['offset'])
        else:
            sql += ' * FROM `%s`' % table
        return sql
